﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Hotel_Project.Tools.MySqlData_Helps
{
    public static class MySQLHelper
    {
        private static string connstr = "server=localhost;user=root;database=hotel;port=3306;password=123456";
        public static MySqlConnection connection;
        //定义数据适配器
        public static MySqlDataAdapter adp;

        #region 连接数据库
        public static void OpenConn()
        {
            if (connection == null)
            {
                connection = new MySqlConnection(connstr);
            }
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }if (connection.State == ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
        }
        #endregion


        #region 执行MySql语句前的准备
        public static void PrepareMySql(string sql)
        {
            OpenConn();
            adp = new MySqlDataAdapter(sql,connection);
        }
        #endregion


        #region 设置参数和获取SQL语句的参数
        public static void SetParameters(string paramaterName,object paramaterValue)
        {
            paramaterName="@"+paramaterName.Trim();
            if (paramaterValue == null ) 
                paramaterValue = DBNull.Value;
            adp.SelectCommand.Parameters.Add(new MySqlParameter(paramaterName, paramaterValue));
        }
        #endregion


        #region 执行非查询操作
        public static int ExecuteNonQuery()
        {
            int result = adp.SelectCommand.ExecuteNonQuery();
            connection.Close();
            return result;
        }
        #endregion


        
        #region 执行查询操作
        public static DataTable ExecuteQuery()
        {
            DataTable dt = new DataTable();
            adp.Fill(dt);
            connection.Close();
            return dt;
            
        }
        #endregion
        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <returns>查询结果第一行第一列</returns>
        public static object ExecuteScalar()
        {
            object obj = adp.SelectCommand.ExecuteScalar();
            connection.Close();
            return obj;
        }
        public static MySqlDataReader ExecDataReader()
        {
            return adp.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}
